﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public sealed class CommandDAL : ICommandDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public CommandDAL()
        {
        }
        
        public IList<CommandInfo> ListCommandInfo(CommandInfo cmdInfo)
        {
            string strSQL = "";
            string TranType = cmdInfo.TranType;
            int nCmdTypeSeq = 0;
            if (cmdInfo.CmdType != null)
                nCmdTypeSeq = cmdInfo.CmdType.Sequence;

            int nEmpType = 0;
            if (cmdInfo.EmployeeType != null)
                nEmpType = cmdInfo.EmployeeType.Sequence;

            List<CommandInfo> InfoList = new List<CommandInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", cmdInfo.Sequence);
                dbManager.AddParameters(2, "@CmdTypeSeq", nCmdTypeSeq);
                dbManager.AddParameters(3, "@EmpTypeSeq", nEmpType);

                if (TranType == SystemInfo._CmdJobSite)
                {
                    strSQL = "SELECT c.JOB_SITE_CMD_SEQ, c.CMD_FLAG, c.CMD_DRAFT_NO, c.CMD_REF_NO"
                            + ", c.CMD_DESCR, c.CMD_STATUS, c.APPROVED_BY"
                            + ", CASE WHEN c.APPROVED_BY IS NULL THEN c.APPROVED_NAME"
                                + " ELSE CAST(CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS CHAR)"
                                + " END AS APPROVED_NAME"
                            + ", c.APPROVED_POSITIONS"
                            + ", c.APPROVED_DATE, c.REMARKS, c.RECORD_STATUS, c.CREATED_BY, c.UPDATED_BY"
                            + ", c.JOB_SITE_TYPE_SEQ, NULL AS EMP_TYPE_SEQ"
                            + ", CASE c.CMD_STATUS WHEN '" + SystemInfo._ApproveRecordStatus + "' THEN 'คำสั่ง'"
                                + " WHEN '" + SystemInfo._WorkRecordStatus + "' THEN 'ร่างคำสั่ง'"
                                + " ELSE '' END AS CMD_STATUS_NAME"
                            + ", null AS CMD_DRAFT_REF_NO"
                            + ", null AS CMD_NAME, null AS RPT_HEADER, null AS RPT_FORMAT"
                            + ", 'กรมส่งเสริมการส่งออก' AS JOB_SITE_NAME"
                        + " FROM HRM_MP_JOB_SITE_COMMANDS c"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON c.APPROVED_BY = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " WHERE c.RECORD_STATUS = @ActiveRecord"
                        + (cmdInfo.Sequence > 0 ? " AND c.JOB_SITE_CMD_SEQ = @Sequence" : "");
                }
                else if (TranType == SystemInfo._CmdEmployee)
                {
                    strSQL = "SELECT c.CMD_SEQ, c.CMD_FLAG, c.CMD_DRAFT_NO, c.CMD_REF_NO"
                            + ", c.CMD_NAME, c.CMD_STATUS, c.APPROVED_SEQ"
                            + ", CASE WHEN c.APPROVED_SEQ IS NULL THEN c.APPROVED_NAME"
                                + " ELSE CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME)"
                                + " END AS APPROVED_NAME"
                            + ", c.APPROVED_POSITIONS"
                            + ", c.APPROVED_DATE, c.REMARKS, c.RECORD_STATUS, c.CREATED_BY, c.UPDATED_BY"
                            + ", c.CMD_TYPE_SEQ, c.EMP_TYPE_SEQ "
                            + ", CASE c.CMD_STATUS WHEN '" + SystemInfo._ApproveRecordStatus + "' THEN 'คำสั่ง'"
                                + " WHEN '" + SystemInfo._WorkRecordStatus + "' THEN 'ร่างคำสั่ง'"
                                + " ELSE '' END AS CMD_STATUS_NAME"
                            + ", CASE c.CMD_STATUS WHEN '" + SystemInfo._ApproveRecordStatus + "' THEN c.CMD_REF_NO"
                                + " WHEN '" + SystemInfo._WorkRecordStatus + "' THEN c.CMD_DRAFT_NO"
                                + " ELSE '' END AS CMD_DRAFT_REF_NO"
                            + ", c.CMD_DESCR, c.RPT_HEADER, ct.RPT_FORMAT"
                            + ", 'กรมส่งเสริมการส่งออก' AS JOB_SITE_NAME"
                        + " FROM HRM_PS_COMMANDS c"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON c.APPROVED_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_PS_COMMAND_TYPES ct ON c.CMD_TYPE_SEQ = ct.CMD_TYPE_SEQ"
                        + " WHERE c.RECORD_STATUS = @ActiveRecord"
                        + (nCmdTypeSeq > 0 ? " AND c.CMD_TYPE_SEQ = @CmdTypeSeq" : "")
                        + (nEmpType > 0 ? " AND c.EMP_TYPE_SEQ = @EmpTypeSeq" : "")
                        + (cmdInfo.Sequence > 0 ? " AND c.CMD_SEQ = @Sequence" : "");
                }
                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    CommandTypeInfo ctInfo = new CommandTypeInfo();
                    ctInfo.Sequence = rdr.GetInt32(14);

                    EmployeeTypeDAL etDAL = new EmployeeTypeDAL();
                    EmployeeTypeInfo etInfo = new EmployeeTypeInfo();
                    if (!rdr.IsDBNull(15))
                    {
                        IList<EmployeeTypeInfo> etList = etDAL.ListEmployeeTypes(new EmployeeTypeInfo(rdr.GetInt32(15)));
                        etInfo = etList.Count > 0 ? etList[0] : null;
                    }

                    CommandInfo Info = new CommandInfo(rdr.GetInt32(0)
                        , rdr.IsDBNull(1) ? "" : rdr.GetString(1)
                        , rdr.IsDBNull(2) ? "" : rdr.GetString(2)
                        , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                        , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                        , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                        , ""
                        , rdr.IsDBNull(6) ? (int?)null : rdr.GetInt32(6)
                        , rdr.IsDBNull(7) ? "" : rdr.GetString(7)
                        , rdr.IsDBNull(8) ? "" : rdr.GetString(8)
                        , rdr.IsDBNull(9) ? (DateTime?)null : rdr.GetDateTime(9)
                        , rdr.IsDBNull(10) ? "" : rdr.GetString(10)
                        , rdr.IsDBNull(11) ? "" : rdr.GetString(11)
                        , rdr.IsDBNull(12) ? (int?)null : rdr.GetInt32(12)
                        , rdr.IsDBNull(13) ? (int?)null : rdr.GetInt32(13)
                        );
                    Info.StatusName = rdr.IsDBNull(16) ? "" : rdr.GetString(16);
                    Info.EmployeeType = etInfo;
                    Info.CmdType = ctInfo;
                    Info.DraftRefNo = rdr.IsDBNull(17) ? "" : rdr.GetString(17);
                    Info.Descriptions = rdr.IsDBNull(18) ? "" : rdr.GetString(18);
                    Info.ReportHeader = rdr.IsDBNull(19) ? "" : rdr.GetString(19);
                    InfoList.Add(Info);
                }

                return InfoList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {

                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
        
        public DataSet LoadCommands(CommandInfo Info)
        {
            string strSQL = "";
            string TranType = Info.TranType;
            int nCmdTypeSeq = 0;
            if (Info.CmdType != null)
                nCmdTypeSeq = Info.CmdType.Sequence;

            int nEmpType = 0;
            if (Info.EmployeeType != null)
                nEmpType = Info.EmployeeType.Sequence;

            DataSet ds = null;
            
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", Info.Sequence);
                dbManager.AddParameters(2, "@CmdTypeSeq", nCmdTypeSeq);
                dbManager.AddParameters(3, "@EmpTypeSeq", nEmpType);

                if (TranType == SystemInfo._CmdJobSite)
                {
                    strSQL = "SELECT c.JOB_SITE_CMD_SEQ, c.CMD_FLAG, c.CMD_DRAFT_NO, c.CMD_REF_NO"
                            + ", c.CMD_DESCR, c.CMD_STATUS, c.APPROVED_BY"
                            + ", CASE WHEN c.APPROVED_BY IS NULL THEN CAST(c.APPROVED_NAME AS CHAR)"
                                + " ELSE CAST(CONCAT(np.PREFIX_NAME, e.FIRST_NAME, CAST(' ' AS CHAR), e.LAST_NAME) AS CHAR)"
                                + " END AS APPROVED_NAME"
                            + ", c.APPROVED_POSITIONS"
                            + ", c.APPROVED_DATE, c.REMARKS, c.RECORD_STATUS, c.CREATED_BY, c.UPDATED_BY"
                            + ", c.JOB_SITE_TYPE_SEQ, NULL AS EMP_TYPE_SEQ"
                            + ", rs.RECORD_STATUS_NAME"
                        + " FROM HRM_MP_JOB_SITE_COMMANDS c"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON c.APPROVED_BY = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN MAS_RECORD_STATUSES rs ON c.CMD_STATUS = rs.RECORD_STATUS"
                        + " WHERE c.RECORD_STATUS = @ActiveRecord"
                        + (Info.Sequence > 0 ? " AND c.JOB_SITE_CMD_SEQ = @Sequence" : "");
                }
                else if (TranType == SystemInfo._CmdEmployee)
                {
                    strSQL = "SELECT c.CMD_SEQ, c.CMD_FLAG, c.CMD_DRAFT_NO, c.CMD_REF_NO"
                            + ", c.CMD_NAME, c.CMD_STATUS, c.APPROVED_SEQ, c.CMD_DESCR, c.RPT_HEADER"
                            + ", CASE WHEN c.APPROVED_SEQ IS NULL THEN CAST(c.APPROVED_NAME AS CHAR)"
                                + " ELSE CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME)"
                                + " END AS APPROVED_NAME"
                            + ", c.APPROVED_POSITIONS"
                            + ", c.APPROVED_DATE, c.REMARKS, c.RECORD_STATUS, c.CREATED_BY, c.UPDATED_BY"
                            + ", c.CMD_TYPE_SEQ, c.EMP_TYPE_SEQ "
                            + ", CASE c.CMD_STATUS WHEN '" + SystemInfo._ApproveRecordStatus + "' THEN 'คำสั่ง'"
                                + " WHEN '" + SystemInfo._WorkRecordStatus + "' THEN 'ร่างคำสั่ง'"
                                + " ELSE '' END AS CMD_STATUS_NAME"
                            + ", 'กรมส่งเสริมการส่งออก' AS JOB_SITE_NAME"
                            + ", CONCAT('บัญชีรายละเอียด', c.CMD_NAME, ' แนบท้ายคำสั่งที่ ', c.CMD_REF_NO, ' ลงวันที่่ ', REPLACE(CONVERT(VARCHAR(11),DATEADD( year, 543 , c.APPROVED_DATE),106), ' ','/')) AS CMD_HEADER"
                        + " FROM HRM_PS_COMMANDS c"
                        + " LEFT JOIN HRM_PS_EMPLOYEES e ON c.APPROVED_SEQ = e.EMP_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_PS_COMMAND_TYPES ct ON c.CMD_TYPE_SEQ = ct.CMD_TYPE_SEQ"
                        + " WHERE c.RECORD_STATUS = @ActiveRecord"
                        + (nCmdTypeSeq > 0 ? " AND c.CMD_TYPE_SEQ = @CmdTypeSeq" : "")
                        + (nEmpType > 0 ? " AND c.EMP_TYPE_SEQ = @EmpTypeSeq" : "")
                        + (Info.Sequence > 0 ? " AND c.CMD_SEQ = @Sequence" : "");
                }

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return ds;
        }
        public DataSet LoadCommandType(string CmdGroup)
        {
            try
            {
                string sql;
                string Filter = "";

                if (CmdGroup == "ADD")
                    Filter = " AND CMD_GROUP_SEQ = 1";
                else if (CmdGroup == "GROW")
                    Filter = " AND CMD_GROUP_SEQ = 2";
                else if (CmdGroup == "MOVE")
                    Filter = " AND CMD_GROUP_SEQ IN(3, 4)";
                else if (CmdGroup == "GROWMOVE")
                    Filter = " AND CMD_GROUP_SEQ IN(2,3,4)";

                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);

                sql = " SELECT * "
                        + ", CONCAT(CMD_TYPE_NAME_SHORT, ' - ', CMD_TYPE_NAME) AS CMD_TYPE_DESCR"
                    + " FROM HRM_PS_COMMAND_TYPES "
                    + " WHERE RECORD_STATUS = @RecordStatus"
                        + Filter;

                DataSet ds = new DataSet();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);

                return ds;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public string GetNextDraftNo(string CommandTypeSeq)
        {
            string strResult = "";
            int nNextRunningNo;
            DataTable dt, dtb;
            string strSQL = "";
            dbManager.Open();
            dbManager.CreateParameters(2);

            dbManager.AddParameters(0, "@CommandType", CommandTypeSeq);
            dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

            if (CommandTypeSeq == SystemInfo._CmdJobSite)
            {
                // strSQL = "SELECT CAST('JS' AS CHAR) AS CMD_TYPE_NAME_SHORT"
                strSQL = "SELECT 'JS' AS CMD_TYPE_NAME_SHORT"
                    + " FROM HRM_MP_JOB_SITE_COMMANDS"
                    + " WHERE RECORD_STATUS = @ActiveRecord";
                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dt.Rows.Count > 0)
                    strResult = dt.Rows[0]["CMD_TYPE_NAME_SHORT"].ToString();

                strResult += "-" + string.Format("{0:yyyyMMdd}", DateTime.Today) + "-";
                strSQL = "SELECT MAX(CMD_DRAFT_NO) AS MAX_DRAFT_NO"
                    + " FROM HRM_MP_JOB_SITE_COMMANDS"
                    + " WHERE CMD_DRAFT_NO LIKE '" + strResult + "%'";
            }
            else if (CommandTypeSeq == SystemInfo._CmdPosition)
            {
                
            }
            else
            {
                strSQL = "SELECT CMD_TYPE_NAME_SHORT"
                    + " FROM HRM_PS_COMMAND_TYPES"
                    + " WHERE CMD_TYPE_SEQ = @CommandType"
                    + " AND RECORD_STATUS = @ActiveRecord";

                dt = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

                if (dt.Rows.Count > 0)
                    strResult = dt.Rows[0]["CMD_TYPE_NAME_SHORT"].ToString();

                strResult += "-" + string.Format("{0:yyyyMMdd}", DateTime.Today) + "-";
                strSQL = "SELECT MAX(CMD_DRAFT_NO) AS MAX_DRAFT_NO"
                    + " FROM HRM_PS_COMMANDS"
                    + " WHERE CMD_DRAFT_NO LIKE '" + strResult + "%'";
            }
            
            dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            if (dtb.Rows.Count > 0)
            {
                string strText = dtb.Rows[0]["MAX_DRAFT_NO"].ToString();
                strText = strText.Length > 0 ? strText : "000";
                nNextRunningNo = int.Parse(strText.Substring(strText.Length - 3, 3)) + 1;
               // nNextRunningNo = int.Parse(string strResult string.ri.Right(Misc.NullValue(drc[0][0], "0000").ToString(), 4)) + 1;
                strResult += nNextRunningNo.ToString("000");
            }

            return strResult;
        }
        public void Insert(CommandInfo Info)
        {
            string strSQL = "";
            string TranType = Info.TranType;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@cmd_draft_no", Info.DraftNo);
                dbManager.AddParameters(1, "@cmd_ref_no", Info.RefNo);
                dbManager.AddParameters(2, "@cmd_descr", Info.Descriptions);
                dbManager.AddParameters(3, "@cmd_status", Info.Status);
                dbManager.AddParameters(4, "@cmd_flag", Info.CmdFlag);
                dbManager.AddParameters(5, "@approved_by", Info.ApprovedBy);
                dbManager.AddParameters(6, "@approved_name", Info.ApprovedName);
                dbManager.AddParameters(7, "@approved_positions", Info.ApprovedPositions);
                dbManager.AddParameters(8, "@approved_date", Info.ApprovedDate);
                dbManager.AddParameters(9, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(10, "@created_by", Info.CreatedBy);
                dbManager.AddParameters(11, "@created_when", DateTime.Today);
                dbManager.AddParameters(12, "@job_site_type", Int32.Parse(Info.CmdFlag));
                dbManager.AddParameters(13, "@cmd_type_seq", Info.CmdType == null ? (int?)null : Info.CmdType.Sequence);
                dbManager.AddParameters(14, "@emp_type_seq", Info.EmployeeType == null ? (int?)null : Info.EmployeeType.Sequence);
                dbManager.AddParameters(15, "@remarks", Info.Remarks);

                dbManager.AddParameters(16, "@cmd_name", Info.Name);
                dbManager.AddParameters(17, "@rpt_header", Info.ReportHeader);

                if (TranType == SystemInfo._CmdJobSite)
                {
                    strSQL = "INSERT INTO HRM_MP_JOB_SITE_COMMANDS"
                        + "(CMD_DRAFT_NO, CMD_REF_NO, CMD_DESCR, CMD_STATUS, CMD_FLAG"
                            + ", APPROVED_BY, APPROVED_NAME, APPROVED_POSITIONS, APPROVED_DATE"
                            + ", JOB_SITE_TYPE_SEQ, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " VALUES (@cmd_draft_no, @cmd_ref_no, @cmd_descr, @cmd_status"
                            + ", @cmd_flag, @approved_by, @approved_name, @approved_positions"
                            + ", @approved_date, @job_site_type, @record_status, @created_by, @created_when)";
                }
                else if (TranType == SystemInfo._CmdEmployee)
                {
                    strSQL = "INSERT INTO HRM_PS_COMMANDS"
                        + "(CMD_FLAG, CMD_DRAFT_NO, CMD_NAME, CMD_DESCR, RPT_HEADER, CMD_REF_NO, CMD_TYPE_SEQ, EMP_TYPE_SEQ"
                        + ", CMD_STATUS, APPROVED_SEQ, APPROVED_NAME, APPROVED_POSITIONS, APPROVED_DATE"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                        + " VALUES (@cmd_flag, @cmd_draft_no, @cmd_name, @cmd_descr, @rpt_header, @cmd_ref_no, @cmd_type_seq, @emp_type_seq"
                        + ", @cmd_status, @approved_by, @approved_name, @approved_positions, @approved_date"
                        + ", @remarks, @record_status, @created_by, @created_when)";
                }
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Update(CommandInfo Info)
        {
            string strSQL = "";
            string TranType = Info.TranType;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@cmd_draft_no", Info.DraftNo);
                dbManager.AddParameters(1, "@cmd_ref_no", Info.RefNo);
                dbManager.AddParameters(2, "@cmd_descr", Info.Descriptions);
                dbManager.AddParameters(3, "@cmd_status", Info.Status);
                dbManager.AddParameters(4, "@cmd_flag", Info.CmdFlag);
                dbManager.AddParameters(5, "@approved_by", Info.ApprovedBy);
                dbManager.AddParameters(6, "@approved_name", Info.ApprovedName);
                dbManager.AddParameters(7, "@approved_positions", Info.ApprovedPositions);
                dbManager.AddParameters(8, "@approved_date", Info.ApprovedDate);
                dbManager.AddParameters(9, "@Sequence", Info.Sequence);
                dbManager.AddParameters(10, "@updated_by", Info.UpdatedBy);
                dbManager.AddParameters(11, "@updated_when", DateTime.Today);
                dbManager.AddParameters(12, "@job_site_type", Int32.Parse(Info.CmdFlag));
                dbManager.AddParameters(13, "@cmd_type_seq", Info.CmdType == null ? (int?)null : Info.CmdType.Sequence);
                dbManager.AddParameters(14, "@emp_type_seq", Info.EmployeeType == null ? (int?)null : Info.EmployeeType.Sequence);
                dbManager.AddParameters(15, "@remarks", Info.Remarks);

                dbManager.AddParameters(16, "@cmd_name", Info.Name);
                dbManager.AddParameters(17, "@rpt_header", Info.ReportHeader);

                if (TranType == SystemInfo._CmdJobSite)
                {
                    strSQL = "UPDATE HRM_MP_JOB_SITE_COMMANDS"
                        + " SET CMD_DRAFT_NO = @cmd_draft_no"
                            + ", CMD_REF_NO = @cmd_ref_no"
                            + ", CMD_DESCR = @cmd_descr"
                            + ", CMD_STATUS = @cmd_status"
                            + ", CMD_FLAG = @cmd_flag"
                            + ", JOB_SITE_TYPE_SEQ = @job_site_type"
                            + ", APPROVED_BY = @approved_by"
                            + ", APPROVED_NAME = @approved_name"
                            + ", APPROVED_DATE = @approved_date"
                            + ", APPROVED_POSITIONS = @approved_positions"
                            + ", UPDATED_BY = @updated_by"
                            + ", UPDATED_WHEN = @updated_when"
                        + " WHERE JOB_SITE_CMD_SEQ = @Sequence";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                    dbManager.CommitTransaction();

                    if (Info.Status == SystemInfo._ApproveRecordStatus)
                    {
                        strSQL = "CALL P_Auto_update_jobsite_commands(@Sequence)";
                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                        dbManager.CommitTransaction();
                    }
                }
                else if (TranType == SystemInfo._CmdEmployee)
                {
                    strSQL = "UPDATE HRM_PS_COMMANDS"
                        + " SET CMD_FLAG = @cmd_flag"
                            + ", CMD_DRAFT_NO = @cmd_draft_no"
                            + ", CMD_NAME = @cmd_name"
                            + ", CMD_DESCR = @cmd_descr"
                            + ", RPT_HEADER = @rpt_header"
                            + ", CMD_REF_NO = @cmd_ref_no"
                            + ", CMD_TYPE_SEQ = @cmd_type_seq"
                            + ", EMP_TYPE_SEQ = @emp_type_seq"
                            + ", CMD_STATUS = @cmd_status"
                            + ", REMARKS = @remarks"
                            + ", APPROVED_SEQ = @approved_by"
                            + ", APPROVED_NAME = @approved_name"
                            + ", APPROVED_DATE = @approved_date"
                            + ", APPROVED_POSITIONS = @approved_positions"
                            + ", UPDATED_BY = @updated_by"
                            + ", UPDATED_WHEN = @updated_when"
                        + " WHERE CMD_SEQ = @Sequence";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                    dbManager.CommitTransaction();

                    if (Info.Status == SystemInfo._ApproveRecordStatus)
                    {
                        strSQL = "CALL P_Auto_Update_Employee_Status(@Sequence)";
                        dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                        dbManager.CommitTransaction();
                    }
                }
                
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public void Delete(string TranType, int Sequence, int UpdatedBy)
        {
            string strSQL = "";

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                if (TranType == SystemInfo._CmdJobSite)
                {
                    strSQL = "UPDATE HRM_MP_JOB_SITE_COMMANDS"
                        + " SET RECORD_STATUS = @record_status"
                            + ", UPDATED_BY = @updated_by"
                            + ", UPDATED_WHEN = @updated_when"
                        + " WHERE JOB_SITE_CMD_SEQ = @Sequence";
                }
                else if (TranType == SystemInfo._CmdEmployee)
                {
                    strSQL = "UPDATE HRM_PS_COMMANDS"
                        + " SET RECORD_STATUS = @record_status"
                            + ", UPDATED_BY = @updated_by"
                            + ", UPDATED_WHEN = @updated_when"
                        + " WHERE CMD_SEQ = @Sequence";
                }
                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }
    }
}
